package com.zrwl.poscloud.commons.utils;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author *****
 * @Date 2020-11-03 10:30
 */
@Slf4j
public class ExcelUtil {
    public static final char SBC_SPACE = 12288; // 全角空格 12288

    public static final char DBC_SPACE = 32; //半角空格 32

    // ASCII character 33-126 <-> unicode 65281-65374
    public static final char ASCII_START = 33;

    public static final char ASCII_END = 126;

    public static final char UNICODE_START = 65281;

    public static final char UNICODE_END = 65374;

    public static final char DBC_SBC_STEP = 65248; // 全角半角转换间隔

    public ExcelUtil() {
        throw new Error("工具类不允许实例化！");
    }

    /**
     * 获取并解析excel文件，返回一个二维集合
     *
     * @param file 上传的文件
     * @return 二维集合（第一重集合为行，第二重集合为列，每一行包含该行的列集合，列集合包含该行的全部单元格的值）
     */
    public static List<Map<String, String>> ArrayList(MultipartFile file) {
        List<Map<String, String>> row = new ArrayList<Map<String, String>>();
//获取文件名称
        String fileName = file.getOriginalFilename();
        System.out.println(fileName);

        try {
//获取输入流
            InputStream in = file.getInputStream();
//判断excel版本
            Workbook workbook = null;
            if (judegExcelEdition(fileName)) {
                workbook = new XSSFWorkbook(in);
            } else {
                workbook = new HSSFWorkbook(in);
            }
            //获取第一张工作表
            Sheet sheet = workbook.getSheetAt(0);
            //获取到excel行数 ---> 1003(从0开始)
            int num = sheet.getLastRowNum();
            //删除的空行(包含空白行和带有格式的空行)
            int bk = 0;
            //记录空白行的集合
            List<Integer> nums = new ArrayList();
            for (int i = 1; i <= num; i++) {
                Row row1 = sheet.getRow(i);
                //获取当前行的列数 ---> 1003(从0开始)
                int cellNum = row1.getLastCellNum();
                boolean flag = false;
                //带有格式的空白行
                if (row1 != null) {
                    for (Cell cell : row1) {
                        //判断该单元格是否为空
                        if (StringUtils.isEmpty(cell.toString())) {
                            flag = true;
                            cellNum--;
                            break;
                        }
                    }
                    if (flag && cellNum == 0) {
                        nums.add(i);
                        bk++;
                    }
                    //空白行
                } else {
                    nums.add(i);
                    bk++;
                }
            }
            //删除无效数据行(空白行,带格式的空白行)
            for (Integer n : nums) {
                if (sheet.getRow(n) != null) {
                    sheet.removeRow(sheet.getRow(n));
                }
            }
            String headName[];
            if (sheet.getPhysicalNumberOfRows() < 1) {
                throw new RuntimeException("表格为空，请重新上传！");
            } else {
                Row headRow = sheet.getRow(0);
                headName = new String[headRow.getPhysicalNumberOfCells()];
                for (int j = 0; j < headRow.getPhysicalNumberOfCells(); j++) {
                    try {
                        headName[j] = sbc2dbcCase(headRow.getCell(j).getStringCellValue());
                    } catch (Exception e) {
                        throw new RuntimeException("表头异常");
                    }

                }
            }
            log.info("表头识别完成：" + headName.toString());
            for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
                //循环获取工作表的每一行从第2行开始
                Row sheetRow = sheet.getRow(i);
                //循环获取每一列
                Map<String, String> cell = new HashMap<String, String>();
                for (int j = 0; j < sheetRow.getPhysicalNumberOfCells(); j++) {
                    //空单元格处理
                    if (sheetRow.getCell(j) == null) {
                        sheetRow.createCell(j);
                    }
                    //拿到当前单元格
                    Cell sheetRowCell = sheetRow.getCell(j);
                    CellType cellType = sheetRowCell.getCellType();
                    try {
                        //将每一个单元格的值装入列集合
                        if (cellType.NUMERIC.equals(sheetRowCell.getCellType())) {//
                            cell.put(headName[j], importByExcelForDate(sheetRowCell));
                        }
                        if (cellType.BLANK.equals(sheetRowCell.getCellType())) {
                            cell.put(headName[j], "");
                        }
                        if (cellType.STRING.equals(sheetRowCell.getCellType())) {
                            cell.put(headName[j],sbc2dbcCase(sheetRowCell.getStringCellValue()));
                        }
                        if (cellType.BOOLEAN.equals(sheetRowCell.getCellType())) {
                            cell.put(headName[j], Boolean.toString(sheetRowCell.getBooleanCellValue()));
                        }
                        if (cellType.FORMULA.equals(sheetRowCell.getCellType())) {
                            cell.put(headName[j], sheetRowCell.getCellFormula());
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
                //将装有每一列的集合装入大集合
                row.add(cell);
                //关闭资源
                workbook.close();
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            System.out.println("===================未找到文件======================");
        } catch (IOException e) {
            e.printStackTrace();
            System.out.println("===================上传失败======================");
        }
        return row;
    }

    /**
     * 判断上传的excel文件版本（xls为2003，xlsx为2017）
     *
     * @param fileName 文件路径
     * @return excel2007及以上版本返回true，excel2007以下版本返回false
     */
    private static boolean judegExcelEdition(String fileName) {
        if (fileName.matches("^.+\\.(?i)(xls)$")) {
            return false;
        } else {
            return true;
        }
    }

    /**
     * 解析POI导入Excel中日期格式数据
     *
     * @param currentCell
     * @return currentCellValue
     */
    public static String importByExcelForDate(Cell currentCell) {
        String currentCellValue = "";
        // 判断单元格数据是否是日期
        if ("yyyy/mm;@".equals(currentCell.getCellStyle().getDataFormatString())
                || "yyyy\"年\"m\"月\"d\"日\";@".equals(currentCell.getCellStyle().getDataFormatString())
                || "yyyy\"年\"mm\"月\"dd\"日\";@".equals(currentCell.getCellStyle().getDataFormatString())
                || "m/d/yy".equals(currentCell.getCellStyle().getDataFormatString())
                || "yy/m/d".equals(currentCell.getCellStyle().getDataFormatString())
                || "mm/dd/yy".equals(currentCell.getCellStyle().getDataFormatString())
                || "dd-mmm-yy".equals(currentCell.getCellStyle().getDataFormatString())
                || "yyyy/m/d".equals(currentCell.getCellStyle().getDataFormatString())) {
            if (DateUtil.isCellDateFormatted(currentCell)) {
                // 用于转化为日期格式
                Date d = currentCell.getDateCellValue();
                DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");

                currentCellValue = formater.format(d);
            }
        } else {
            // 不是日期原值返回
            double value = currentCell.getNumericCellValue();
            DecimalFormat df = new DecimalFormat("#0.00");
            currentCellValue = df.format(value);
            if (currentCellValue.endsWith(".00")) {
                currentCellValue = currentCellValue.replace(".00", "");
            }

        }

        return currentCellValue;
    }

    /**
     * 全角切半角
     *
     * @param src
     * @return DBC case
     */
    public static String sbc2dbcCase(String src) {
        if (src == null) {
            return null;
        }
        char[] c = src.toCharArray();
        for (int i = 0; i < c.length; i++) {
            c[i] = sbc2dbc(c[i]);
        }
        return new String(c);
    }

    public static char dbc2sbc(char src) {
        if (src == DBC_SPACE) {
            return SBC_SPACE;
        }
        if (src <= ASCII_END) {
            return (char) (src + DBC_SBC_STEP);
        }
        return src;
    }

    /**
     * 半角切全角
     *
     * @param src
     * @return SBC case string
     */
    public static String dbc2sbcCase(String src) {
        if (src == null) {
            return null;
        }

        char[] c = src.toCharArray();
        for (int i = 0; i < c.length; i++) {
            c[i] = dbc2sbc(c[i]);
        }

        return new String(c);
    }

    public static char sbc2dbc(char src) {
        if (src == SBC_SPACE) {
            return DBC_SPACE;
        }

        if (src >= UNICODE_START && src <= UNICODE_END) {
            return (char) (src - DBC_SBC_STEP);
        }

        return src;
    }
}
